<?php

namespace app\admin\dao;

use app\common\model\AccountBill;
use app\common\model\Member;
use app\common\model\Role;
use app\common\model\Trade;
use app\common\model\User;
use app\common\model\UserPersonnel;
use ShidianUtil\Thinkphp\Dao\DaoTrait;
use think\model\Collection;

/**
 * AccountBillDao 数据访问对象
 * Class AccountBillDao
 * @package app\admin\dao
 * @method AccountBill findByPK(?int $id, $field = true)
 * @method \think\Collection selectAll(?int $page, ?int $page_size, $field = true)
 * @method AccountBill insertActive(array $params, array $allow_field = [])
 * @method AccountBill updateActive(AccountBill $model, array $params, array $allow_field = [])
 * @method AccountBill findAndUpdateActive(int $id, array $params, array $allow_field = [])
 * @method AccountBill updateActiveByClosure(\Closure $closure, array $params, array $allow_field = [])
 * @method void deleteModel(AccountBill $model)
 * @method void deleteById(int|array|\Closure $id, bool $force = false)
 * @link https://www.kancloud.cn/manual/thinkphp6_0/1037579
 * @link https://www.kancloud.cn/manual/think-orm/1257998
 * @link https://www.kancloud.cn/thinkphp/master-database-and-model/265551
 */
class AccountBillDao
{
    use DaoTrait;

    /**
     * @var AccountBill
     */
    private $baseModel;

    /**
     * AccountBillDao constructor.
     */
    public function __construct()
    {
        $this->baseModel = AccountBill::class;
    }

    /**
     * 充值消费
     * @param $startTime
     * @param $endTime
     * @return \think\Collection
     */
    public function selectOfSourceForExport($startTime, $endTime): \think\Collection
    {
        $collection = AccountBill::timeRange($startTime, $endTime, 'created', 'created')
            ->where(function ($query) {
                $query->whereOr('accountType', PAYMENT_CARD)
                    ->whereOr('accountType', PAYMENT_COIN)
                    ->whereOr('accountType', PAYMENT_OFFLINE)
                    ->whereOr('billType', TRADE_TOKEN_RECHARGE);
            })
            ->field(true)
            ->select();

        $this->wrapOtherMemberForExport($collection);

        return $collection;
    }

    /**
     * 公司资金流水消费-列表
     * @param $companyId
     * @param $page
     * @param $page_size
     * @return array
     */
    public function listBillByCompany($companyId, $startTime, $endTime, $page, $page_size): array
    {
        $tableName = AccountBill::getTable();

        $query = AccountBill::leftJoin('member f', "$tableName.fromMemberId=f.id and $tableName.fromType='member'")
            ->when(is_id($companyId), fn($query) => $query->where('fromMemberId', $companyId))
            ->where('fromType', 'company')
            ->whereIn('billType', [TRADE_SOURCE, TRADE_AXB])
            ->when(is_numeric($startTime), fn($query) => $query->where("$tableName.created", '>=', $startTime))
            ->when(is_numeric($endTime), fn($query) => $query->where("$tableName.created", '<=', $endTime))
            ->tableField(true, $tableName)
            ->with([
                'from' => fn($query) => $query->field(['id', 'name']),
                'proxyUserPersonnel',
                'proxyMember' => fn($query) => $query->shortMember(),
            ])
            ->withAttr([
                'accountTypeStr' => function ($value, $data) {
                    return account_type_str($data['accountType'], $data['type']);
                }
            ])->append(['accountTypeStr'])
            ->order("$tableName.id", 'DESC')
            ->paging($page, $page_size);

        $data = listOutput($query, $page_size);

        $this->wrapCompany($data['list']);

        return $data;
    }

    /**
     * 公司资金流水消费-列表统计
     * @param $companyId
     * @return array
     */
    public function statisticsListBillByCompany($companyId, $startTime, $endTime): array
    {
        $tableName = AccountBill::getTable();

        $query = AccountBill::leftJoin('member f', "$tableName.fromMemberId=f.id and $tableName.fromType='member'")
            ->when(is_id($companyId), fn($query) => $query->where('fromMemberId', $companyId))
            ->where('fromType', 'company')
            ->whereIn('billType', [TRADE_SOURCE, TRADE_AXB])
            ->when(is_numeric($startTime), fn($query) => $query->where("$tableName.created", '>=', $startTime))
            ->when(is_numeric($endTime), fn($query) => $query->where("$tableName.created", '<=', $endTime));

        return [
            'total_count' => $query->count(),
            'card_sum' => (clone $query)->where('accountType', PAYMENT_CARD)->sum('money'),
            'coin_sum' => (clone $query)->where('accountType', PAYMENT_COIN)->sum('money'),
        ];
    }

    /**
     * 资金流水-列表
     * @param $type
     * @param $billType
     * @param $startTime
     * @param $endTime
     * @param $kw
     * @param $page
     * @param $page_size
     * @return array
     */
    public function listBill($memberId, $type, $billType, $accountType, $startTime, $endTime, $kw, $page, $page_size): array
    {
        $tableName = AccountBill::getTable();

        $query = $this->queryListBill($tableName, $type, $billType, $accountType, $startTime, $endTime, $memberId, $kw);

        $query->with([
            'trade' => fn($query) => $query->field(['id', 'orderNo']),
            'transfer' => fn($query) => $query->field(['id', 'orderNo']),
        ])->withAttr([
            'accountTypeStr' => function ($value, $data) {
                return account_type_str($data['accountType'], $data['type']);
            }
        ])->append(['accountTypeStr'])
            ->order("$tableName.id", 'DESC')
            ->paging($page, $page_size);

        $data = listOutput($query, $page_size);

        $this->wrapOtherMember($data['list']);

        return $data;
    }

    /**
     * 资金流水-列表统计
     * @param $memberId
     * @param $type
     * @param $billType
     * @param $accountType
     * @param $startTime
     * @param $endTime
     * @param $kw
     * @return array
     */
    public function statisticsListBill($memberId, $type, $billType, $accountType, $startTime, $endTime, $kw): array
    {
        $tableName = AccountBill::getTable();

        $query = $this->queryListBill($tableName, $type, $billType, $accountType, $startTime, $endTime, $memberId, $kw);

        return [
            'money_sum' => $query->sum('money'),
            'income_sum' => (clone $query)->where('toType', 'system')->sum('money'),
            'expense_sum' => (clone $query)->where('fromType', 'system')->sum('money'),
        ];
    }

    private function wrapOtherMember(\think\Collection $list)
    {

        // 用户
        $members = $this->getMembers($list);

        // 公司
        $companys = $this->getCompanys($list);

        // 被代付员工模型
        $userPersonnels = $this->getUserPersonnels($list);

        $list->each(function (AccountBill $bill) use ($companys, $members, $userPersonnels) {
            if ($bill->fromType == 'member') {

                $bill->isIncome = '1';
                $bill->afterMoney = $bill->fromAfterMoney;
                $bill->otherType = 'member';

                $other = $members[$bill->fromMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if ($bill->toType == 'member') {

                $bill->isIncome = '0';
                $bill->afterMoney = $bill->toAfterMoney;
                $bill->otherType = 'member';

                $other = $members[$bill->toMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if ($bill->fromType == 'company') {

                $bill->isIncome = '1';
                $bill->afterMoney = $bill->fromAfterMoney;
                $bill->otherType = 'company';

                $other = $companys[$bill->fromMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if ($bill->toType == 'company') {

                $bill->isIncome = '0';
                $bill->afterMoney = $bill->toAfterMoney;
                $bill->otherType = 'company';

                $other = $companys[$bill->toMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if (is_id($bill->proxyUserPersonnelId)) {
                $proxy = $userPersonnels[$bill->proxyUserPersonnelId] ?? null;
                $bill->setRelation('proxyUserPersonnel', $proxy);
            }

            if (is_id($bill->proxyMemberId)) {
                $member = $members[$bill->proxyMemberId] ?? null;
                $bill->setRelation('proxyMember', $member);
            }
        });

    }

    /**
     * 查询账单列表中的用户
     * @param \think\Collection $list
     * @return array|Collection
     */
    private function getMembers(\think\Collection $list)
    {
        // 用户
        $fromMemberIds = $list->filter(fn($bill) => $bill->fromType == 'member')->column('fromMemberId');
        $toMemberIds = $list->filter(fn($bill) => $bill->toType == 'member')->column('toMemberId');
        $proxyMemberIds = $list->filter(fn($bill) => is_id($bill->proxyMemberId))->column('proxyMemberId');
        $ids = array_merge($fromMemberIds, $toMemberIds, $proxyMemberIds);
        $collection = Collection::make();

        if (!empty($ids)) {
            $collection = Member::whereIn('id', $ids)
                ->field(['id', 'nickname', 'realname', 'type', 'phone', 'headImgUrl', 'cropImgUrl'])
                ->append(['headImg', 'headImgThumb', 'cropImg', 'showName'])
                ->hidden(['headImgUrl'])
                ->select()
                ->column(null, 'id');
        }

        return $collection;
    }

    /**
     * 查询账单列表中的公司
     * @param \think\Collection $list
     * @return array|Collection
     */
    private function getCompanys(\think\Collection $list)
    {
        $fromMemberIds = $list->filter(fn($bill) => $bill->fromType == 'company')->column('fromMemberId');
        $toMemberIds = $list->filter(fn($bill) => $bill->toType == 'company')->column('toMemberId');
        $ids = array_merge($fromMemberIds, $toMemberIds);
        $collection = Collection::make();

        if (!empty($ids)) {
            $collection = User::whereIn('id', $ids)
                ->where('roleType', Role::ROLE_TYPE_COMPANY)
                ->field(['id', 'name',])
                ->select()
                ->column(null, 'id');
        }
        return $collection;
    }

    /**
     * 查询账单列表中的被代付员工
     * @param \think\Collection $list
     * @return array|Collection
     */
    private function getUserPersonnels(\think\Collection $list)
    {
        $ids = $list->filter(fn($bill) => is_id($bill->proxyUserPersonnelId))->column('proxyUserPersonnelId');
        $collection = Collection::make();

        if (!empty($ids)) {
            $collection = UserPersonnel::whereIn('id', $ids)
                ->field(['id', 'name', 'mobile'])
                ->select()
                ->column(null, 'id');
        }
        return $collection;
    }

    private function wrapOtherMemberForExport(\think\Collection $list)
    {

        // 用户
        $members = $this->getMembersForExport($list);

        // 公司
        $companys = $this->getCompanysForExport($list);

        $list->each(function (AccountBill $bill) use ($companys, $members) {
            if ($bill->fromType == 'member') {

                $bill->isIncome = '1';
                $bill->afterMoney = $bill->fromAfterMoney;
                $bill->otherType = 'member';

                $other = $members[$bill->fromMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if ($bill->toType == 'member') {

                $bill->isIncome = '0';
                $bill->afterMoney = $bill->toAfterMoney;
                $bill->otherType = 'member';

                $other = $members[$bill->toMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if ($bill->fromType == 'company') {

                $bill->isIncome = '1';
                $bill->afterMoney = $bill->fromAfterMoney;
                $bill->otherType = 'company';

                $other = $companys[$bill->fromMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if ($bill->toType == 'company') {

                $bill->isIncome = '0';
                $bill->afterMoney = $bill->toAfterMoney;
                $bill->otherType = 'company';

                $other = $companys[$bill->toMemberId] ?? null;
                $bill->setRelation('other', $other);
            }

            if (is_id($bill->proxyMemberId)) {
                $member = $members[$bill->proxyMemberId] ?? null;
                $bill->setRelation('proxyMember', $member);
            }
        });

    }

    /**
     * 查询账单列表中的用户
     * @param \think\Collection $list
     * @return array|Collection
     */
    private function getMembersForExport(\think\Collection $list)
    {
        // 用户
        $fromMemberIds = $list->filter(fn($bill) => $bill->fromType == 'member')->column('fromMemberId');
        $toMemberIds = $list->filter(fn($bill) => $bill->toType == 'member')->column('toMemberId');
        $proxyMemberIds = $list->filter(fn($bill) => is_id($bill->proxyMemberId))->column('proxyMemberId');
        $ids = array_merge($fromMemberIds, $toMemberIds, $proxyMemberIds);
        $collection = Collection::make();

        if (!empty($ids)) {

            $tableName = Member::getTable();

            $cardTotalSumSql = AccountBill::where(function ($query) use ($tableName) {
                $query->whereOr(function ($query) use ($tableName) {
                    $query->whereRaw("fromMemberId=$tableName.id")
                        ->where('fromType', 'member');
                })->whereOr(function ($query) use ($tableName) {
                    $query->whereRaw("proxyMemberId=$tableName.id")
                        ->where('fromType', 'company');
                });
            })
                ->where('accountType', 'card')
                ->fieldRaw('sum(`money`)')
                ->buildSql();

            $coinTotalSumSql = AccountBill::where(function ($query) use ($tableName) {
                $query->whereOr(function ($query) use ($tableName) {
                    $query->whereRaw("fromMemberId=$tableName.id")
                        ->where('fromType', 'member');
                })->whereOr(function ($query) use ($tableName) {
                    $query->whereRaw("proxyMemberId=$tableName.id")
                        ->where('fromType', 'company');
                });
            })
                ->where('accountType', 'coin')
                ->fieldRaw('sum(`money`)')
                ->buildSql();


            $collection = Member::whereIn('id', $ids)
                ->field(['id', 'nickname', 'realname', 'type', 'phone', 'headImgUrl', 'cropImgUrl'])
                ->field([
                    $cardTotalSumSql => 'cardTotalSum',
                    $coinTotalSumSql => 'coinTotalSum',
                ])
                ->with([
                    'account',
                    'card',
                    'coin',
                ])
                ->withSum([
                    'tokenRechargeTrades' => fn($query) => $query->where('payStatus', Trade::PAID),
                ], 'payMoney')
                ->withAttr([
                    'cardTotalSum' => fn($value) => $value ?: 0,
                    'coinTotalSum' => fn($value) => $value ?: 0,
                    'token_recharge_trades_sum' => fn($value) => $value ?: 0,
                ])
                ->append(['headImg', 'headImgThumb', 'cropImg', 'showName'])
                ->hidden(['headImgUrl'])
                ->select()
                ->column(null, 'id');
        }

        return $collection;
    }

    /**
     * 查询账单列表中的公司
     * @param \think\Collection $list
     * @return array|Collection
     */
    private function getCompanysForExport(\think\Collection $list)
    {
        $fromMemberIds = $list->filter(fn($bill) => $bill->fromType == 'company')->column('fromMemberId');
        $toMemberIds = $list->filter(fn($bill) => $bill->toType == 'company')->column('toMemberId');
        $ids = array_merge($fromMemberIds, $toMemberIds);
        $collection = Collection::make();

        if (!empty($ids)) {

            $collection = User::whereIn('id', $ids)
                ->where('roleType', Role::ROLE_TYPE_COMPANY)
                ->field(['id', 'name',])
                ->with([
                    'account',
                    'card',
                    'coin',
                ])
                ->withSum([
                    'transactionOffline',
                ], 'amount')
                ->withAttr([
                    'transaction_offline_sum' => fn($value) => $value ?: 0,
                    'cardTotalSum' => fn($value) => $value ?: 0,
                    'coinTotalSum' => fn($value) => $value ?: 0,
                ])
                ->append(['cardTotalSum', 'coinTotalSum'])
                ->select()
                ->column(null, 'id');
        }
        return $collection;
    }

    private function wrapCompany(\think\Collection $list)
    {
        $list->each(function (AccountBill $bill) {
            $bill->setRelation('company', $bill->from);
            $bill->hidden(['from']);
        });

    }

    /**
     * @param $tableName
     * @param $type
     * @param $billType
     * @param $accountType
     * @param $startTime
     * @param $endTime
     * @param $memberId
     * @param $kw
     * @return AccountBill
     */
    private function queryListBill($tableName, $type, $billType, $accountType, $startTime, $endTime, $memberId, $kw)
    {
        $query = AccountBill::when(not_blank($type), fn($query) => $query->where("$tableName.type", $type))
            ->when(not_blank($type), fn($query) => $query->where("$tableName.billType", $billType))
            ->when(not_blank($accountType), fn($query) => $query->where("$tableName.accountType", $accountType))
            ->when(is_numeric($startTime), fn($query) => $query->where("$tableName.created", '>=', $startTime))
            ->when(is_numeric($endTime), fn($query) => $query->where("$tableName.created", '<=', $endTime))
            ->when(not_blank($type), fn($query) => $query->where("$tableName.type", $type));

        if (is_id($memberId) || not_blank($kw)) {
            $query->leftJoin('member f', "$tableName.fromMemberId=f.id and $tableName.fromType='member'")
                ->leftJoin('member t', "$tableName.toMemberId=t.id and $tableName.toType='member'")
                ->when(is_id($memberId), function ($query) use ($memberId) {
                    $query->whereOr(function ($query) use ($memberId) {
                        $query->where('fromMemberId', $memberId)->where('fromType', 'member');
                    })->whereOr(function ($query) use ($memberId) {
                        $query->where('toMemberId', $memberId)->where('toType', 'member');
                    });
                })
                ->search("$tableName.id|f.phone|t.phone|f.nickname|t.nickname|f.realname|t.realname", $kw)
                ->tableField(true, $tableName);
        }
        return $query;
    }

}
